﻿using NORM.DataBase;
using NORM.Entity;
using NORM.SQLObject;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace NORM.Test
{
    public partial class 高级用法 : Form
    {
        public 高级用法()
        {
            InitializeComponent();
        }

        private void btnApply_Click(object sender, EventArgs e)
        {
            try
            {
                var db = NORM.DataBase.DataBaseFactory.Create();

                Models.V_Accounts model = new Models.V_Accounts();
                model.DeleteSign = 0;
                model.Category = "1";
                model.EncryptSign = 0;

                OQL oql = new OQL(model).Where("DeleteSign", model.DeleteSign)
                    .And("Category", model.Category)
                    .Or("EncryptSign", model.EncryptSign)
                    .Select().End;

                PageLimit pager = new PageLimit();
                pager.PageIndex = 1;
                pager.PageSize = 10;

                EntityQuery<Models.T_Accounts>.Instance.QueryList(oql, pager, db);
                int recordCount = pager.RecordCount;
                DataView dv = pager.DataSourceView;

                ///联表操作
                Models.T_Accounts model2 = new Models.T_Accounts();
                //OQL oql2 = new OQL(model).LeftJoin(model2)
                //    .ON("V_Accounts.Category", "T_Accounts.Category")
                //    .Where("T_Accounts.Category", 2)
                //    .Select()
                //    .End;

                List<string> F = new List<string>();
                F.Add("T_Accounts.Category");
                F.Add("T_Accounts.DeleteSign");
                F.Add("V_Accounts.EncryptSign");
                F.Add("V_Accounts.Describ");

                //List<TableField> fields = new List<TableField>();
                //fields.Add(model.PropertyItem<TableField>("EncryptSign"));
                //fields.Add(model.PropertyItem<TableField>("Describ"));
                //fields.Add(model2.PropertyItem<TableField>("Category"));
                //fields.Add(model2.PropertyItem<TableField>("DeleteSign"));

                OQL oql1 = new OQL(model).Where("ID", "=", 1).Select(
                    new string[] { "ID" }
                    ).End;

                OQL oql2 = new OQL(model).LeftJoin(model2)
                   .ON(model.Category, model2.Category)//.ON("V_Accounts.Category", "T_Accounts.Category")
                   .Where(model2.Category, 2)
                    //.Select(F.ToArray())
                   .Select(delegate()
                   {
                       return new List<TableField>(){
               model.produceItem<TableField>("EncryptSign"),
               model.produceItem<TableField>("Describ"),
               model2.produceItem<TableField>("Category"),
               model2.produceItem<TableField>("DeleteSign")};
                   })
                   .End;

                //var db = DataBaseFactory.Create();
                DataTable dt = new DataTable();// db.QueryTable(oql2);
                dt = db.QueryTable(oql2, pager);

                EntityQuery<Models.T_Accounts>.Instance.QueryList(oql2, pager, db);

                Models.T_Category model3 = new Models.T_Category();
                OQL oql3 = new OQL(model3).Where("Name", "个人消费").Select(new string[] { "ID" }).Limit(1).End;//"个人消费"

                Models.T_Accounts model4 = new Models.T_Accounts() { DeleteSign = 0 };
                OQL oql4 = new OQL(model4).Where(new Condition[] {
                //new Condition(){ Field="DeleteSign",Comparison="=",Value=0 },
                model4.produceItem<Condition>("DeleteSign"),
                new Condition(){ Field="Category",Comparison="IN",Value=oql3 }
            }).Select().End;

                var rvl = EntityQuery<Models.T_Accounts>.Instance.QueryList(oql4, db);

                OQL oql5 = new OQL(model4).Join(model3)
                    .ON(model4.Category, model3.ID) //.ON("T_Accounts.Category", "T_Category.ID")
                    .Where(new Condition[] { new Condition() { Field = "T_Accounts.Category", Comparison = "=", Value = oql3 } })
                    .Select(new string[] { "T_Accounts.Describ", "T_Accounts.Category", "T_Category.ID", "T_Category.Name", "T_Category.Describ AS CategoryName" }).End;

                DataTable dt2 = db.QueryTable(oql5);
                //var rv2 = EntityQuery<EntityBase>.Instance.QueryList<Models.T_Accounts>(oql5);

                //OQL oql6 = new OQL(model3).Where(new List<Condition>() { 
                //   new Condition(){ Field="DeleteSign",Value=model3.DeleteSign },
                //   new Condition(){ Field="ID",Value=model3.ID }
                //}).Select().End;

                OQL oql6 = new OQL(model3).Where(new List<Condition>() { 
               new Condition(){ Field="DeleteSign",Value=model3.DeleteSign },
               new Condition(){ Field="ID",Value=model3.ID }}).Select().End;

                DataTable dt3 = db.QueryTable(oql6);

                OQL oql7 = new OQL(model3).Where(GetConditions).GroupBy(new string[] { "Name" }).Select().End;

                DataTable dt4 = db.QueryTable(oql7);

                model3.ID = 4;
                model3.DeleteSign = 0;
                OQL oql8 = new OQL(model3).Where(GetConditions(model3)).Select().End;

                DataTable dt5 = db.QueryTable(oql8);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }          

        }

        private List<Condition> GetConditions()
        {
            Models.T_Category model = new Models.T_Category();
            model.DeleteSign = 0;
            model.ID = 2;          

            return new List<Condition>() { 
               new Condition(){ Field="DeleteSign",Value=model.DeleteSign },
               new Condition(){ Field="ID",Value=model.ID }
            };
        }

        private List<Condition> GetConditions(Models.T_Category model)
        {
            return new List<Condition>() { 
               model.produceItem<Condition>("DeleteSign"),
               model.produceItem<Condition>("ID")
            };
        }

        private void btnApply2_Click(object sender, EventArgs e)
        {
            var db = DataBaseFactory.Create();

            try
            {

                Models.T_Users user = new Models.T_Users();
                user.Name = "系统管理员";
                user.DeleteSign = 0;
                OQL useoql = new OQL(user).Where(new Condition[]{
               user.produceItem<Condition>("Name"),
               user.produceItem<Condition>("DeleteSign")
            })
                 .GroupBy(new string[] { user.produceItem<TableField>("Name").Name })
                 .Select().End;

                DataTable dt = db.QueryTable(useoql);

                Models.T_Category category = new Models.T_Category();
                OQL categoryoql = new OQL(category).Where("DeleteSign", 0).And("Name", "个人消费")
                    .Select(new string[] { "ID" }).End;

                DataTable dt2 = db.QueryTable(categoryoql);

                Models.T_Accounts account = new Models.T_Accounts();

                OQL accountoql = new OQL(account)
                .Where(new Condition[]{
               new Condition(){ Field="DeleteSign",Value=0 },
               new Condition(){ Field="EncryptSign",Value=0 },
               new Condition(){ Field="Category",Value=categoryoql }
            })
                    .Select(new TableField[] { 
                account.produceItem<TableField>("Describ"),
                account.produceItem<TableField>("EncryptSign"),
                account.produceItem<TableField>("Amount"),
                account.produceItem<TableField>("AccountTime")
            }).End;

                DataTable dtt1 = db.QueryTable(accountoql);

                OQL accountoql2 = new OQL(account)
                .Where(new Condition[]{
               new Condition(){ Field="DeleteSign",Value=0 },
               new Condition(){ Field="EncryptSign",Value=0 },
               new Condition(){ Field="Category",Value=categoryoql }
            })
                    .Select(delegate()
                {
                    return new List<TableField>() { 
                account.produceItem<TableField>("Describ"),
                account.produceItem<TableField>("EncryptSign"),
                account.produceItem<TableField>("Amount"),
                account.produceItem<TableField>("AccountTime")
            };
                }).End;

                DataTable dtt2 = db.QueryTable(accountoql2);

                OQL accountoql3 = new OQL(account)
                .Where(new Condition[]{
               new Condition(){ Field="DeleteSign",Value=0 },
               new Condition(){ Field="EncryptSign",Value=0 },
               new Condition(){ Field="Category", Comparison="IN", Value=categoryoql }
            })
                    .Select(() => new
                    {
                        Describ = account.Describ,
                        EncryptSign = account.EncryptSign,
                        Amount = account.Amount,
                        AccountTime = account.AccountTime
                    }).End;

                DataTable dt3 = db.QueryTable(accountoql3);

                Models.T_Roles role = new Models.T_Roles();

                OQL roleoql = OQL.From(role).Where("Name", "责任家长").And("Department", 2).Select(new string[] { "ID" }).Limit(1).End; //.OrderBy(new string[] { "ID ASC" })

                DataTable dt4 = db.QueryTable(roleoql);

                OQL useoql2 = new OQL(user).Where("Name", "王晓东").And("Role", roleoql).Select(new string[] { "UserID" }).Limit(1).End; //.OrderBy(new string[] { "UserID DESC" })

                DataTable dt5 = db.QueryTable(useoql2);

                OQL accountoql4 = new OQL(account)
                    .Where("DeleteSign", 0)
                    .And("CreateBy", useoql2)
                    .OrderBy(w => w.Asc(account.ID) & w.Desc(account.CreateDate))
                    .Select()
                    .End;

                DataTable dt6 = db.QueryTable(accountoql4);

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }
    }
}
